﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace AIM.Common
{
    public class CSV
    {
        /// <summary>
        /// 读取Excel文件，将其存储在DataSet中
        /// </summary>
        /// <param name="fileName">Excel文件</param>
        /// <param name="tableName">Excel表名称</param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string fileName, string tableName)
        {
            string strConn = "Provider=MICROSOFT.JET.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
            OleDbConnection oleConn = new OleDbConnection(strConn);
            string strExcel = "select * from [" + tableName + "]";
            DataSet ds = new DataSet();
            try
            {
                oleConn.Open();
                OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strExcel, oleConn);
                oleAdapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                oleConn.Close();
                oleConn.Dispose();
            }
        }
        /// <summary>
        /// DataTabel转成所有excel字符串
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToExcelStr(DataTable dt)
        {
            StringBuilder data = new StringBuilder("");

            data.Append("<html><head><meta http-equiv=Content-Type content=\"text/html; charset=utf-8\"><body>");
            data.Append("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">");
            //写出列名
            data.Append("<tr style=\"font-weight: bold; white-space: nowrap;\">");
            foreach (DataColumn column in dt.Columns)
            {
                data.Append("<td>");
                data.Append(column.ColumnName);
                data.Append("</td>");
            }
            data.Append("</tr>");

            //写出数据
            foreach (DataRow row in dt.Rows)
            {
                data.Append("<tr>");
                foreach (DataColumn column in dt.Columns)
                {
                    data.Append("<td>");
                    data.Append(row[column].ToString());
                    data.Append("</td>");
                }
                data.Append("</tr>");
            }
            data.Append("</table>");
            data.Append("</body></html>");
            return data.ToString();
        }
    }
}
